<?php
class sis_anggota_model extends MY_Model {
	var $model = 'sis_anggota';
	
	function GetJSON()
	{
		$ls_sort = post('sort');
		if($ls_sort == 'nama_status'){ $ls_sort = 'A.ST_AKTIF'; }
		
		$ls_sql	= "	SELECT	A.ID_ANGGOTA, A.ST_AKTIF, 
							(CASE NVL(A.ST_AKTIF,'0') WHEN '0' THEN 'Daftar' WHEN '1' THEN 'Aktif' ELSE 'Non Aktif' END) NAMA_STATUS,
							A.KD_BANK, F.NAMA_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							B.PERSON_ID, B.ID_KARYAWAN_PK, B.GENDER_ID, B.ID_AGAMA_PK, B.ID_JENJANG_PK, B.KD_KANTOR, B.FULL_NAME, B.MOTHER_NAME,
							B.IDENTITY_TYPE_CODE, B.IDENTITY_NUMBER, B.NIP, B.BIRTH_PLACE, TO_CHAR(B.BIRTH_DATE,'DD/MM/YYYY') BIRTH_DATE, B.EMAIL, B.PHONE, B.USER_ID, 
							C.KD_JENIS_ANGGOTA, C.NAMA_JENIS_ANGGOTA, 
							D.ADDRESS_TYPE_CODE, D.ADDRESS, D.RT, D.RW, D.VILLAGE, D.SUBDISTRICT, 
							D.CITY_CODE, D.PROVINCE_CODE, D.COUNTRY_CODE, D.ZIPCODE, E.GENDER_NAME,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TGL_IURAN') TGL_IURAN,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'PLAFOND') PLAFOND,
							(SELECT TO_CHAR(MIN(X.TGL_STATUS),'DD/MM/YYYY') TGL_STATUS FROM SIS_ANGGOTA_HISTORY X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.KODE_STATUS_ANGGOTA = 'ST001') TGL_DAFTAR
					FROM	SIS_ANGGOTA A 
							LEFT JOIN SW_PERSON B ON A.PERSON_ID = B.PERSON_ID 
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA C ON A.KD_JENIS_ANGGOTA = C.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERSON_ADDRESS D ON B.PERSON_ID = D.PERSON_ID  AND D.ADDRESS_TYPE_CODE = 'ADR01' 
							LEFT JOIN SW_GENDER E ON B.GENDER_ID = E.GENDER_ID
							LEFT JOIN KEU_BANK F ON A.KD_BANK = F.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON A.ID_PERUSAHAAN = G.ID_PERUSAHAAN
					WHERE	A.DELETE_DATE IS NULL
							".(post('kd_jenis_anggota') != '' ? " AND A.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".(post('st_aktif') != '' ? " AND A.ST_AKTIF = '".post('st_aktif')."' " : '')."
							".(get('st_aktif') != '' ? " AND A.ST_AKTIF = '".get('st_aktif')."' " : '')."
							AND UPPER(NVL(g.id_perusahaan,'')) LIKE '%".strtoupper(post('id_perusahaan'))."%'
							AND UPPER(NVL(g.kode_perusahaan,'')) LIKE '%".strtoupper(post('kode_perusahaan'))."%'
							AND UPPER(NVL(g.nama_perusahaan,'')) LIKE '%".strtoupper(post('nama_perusahaan'))."%'
							
							AND (UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.NAMA_JENIS_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(B.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(B.MOTHER_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(B.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							
					ORDER BY ".($ls_sort=='' ? " NVL(A.ST_AKTIF,'0'), A.ID_ANGGOTA " : $ls_sort)." ".(post('order')=='' ? 'asc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetCetakKartuJSON()
	{
		$ls_sql	= "	SELECT	A.ID_ANGGOTA, A.ST_AKTIF, 
							(CASE NVL(A.ST_AKTIF,'0') WHEN '0' THEN 'Daftar' WHEN '1' THEN 'Aktif' ELSE 'Non Aktif' END) NAMA_STATUS,
							A.KD_BANK, F.NAMA_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							B.PERSON_ID, B.ID_KARYAWAN_PK, B.GENDER_ID, B.ID_AGAMA_PK, B.ID_JENJANG_PK, B.KD_KANTOR, B.FULL_NAME, B.MOTHER_NAME,
							B.IDENTITY_TYPE_CODE, B.IDENTITY_NUMBER, B.NIP, B.BIRTH_PLACE, TO_CHAR(B.BIRTH_DATE,'DD/MM/YYYY') BIRTH_DATE, B.EMAIL, B.PHONE, B.USER_ID, 
							C.KD_JENIS_ANGGOTA, C.NAMA_JENIS_ANGGOTA, 
							D.ADDRESS_TYPE_CODE, D.ADDRESS, D.RT, D.RW, D.VILLAGE, D.SUBDISTRICT, 
							D.CITY_CODE, D.PROVINCE_CODE, D.COUNTRY_CODE, D.ZIPCODE, E.GENDER_NAME,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TGL_IURAN') TGL_IURAN,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'PLAFOND') PLAFOND,
							(SELECT TO_CHAR(MIN(X.TGL_STATUS),'DD/MM/YYYY') TGL_STATUS FROM SIS_ANGGOTA_HISTORY X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.KODE_STATUS_ANGGOTA = 'ST001') TGL_DAFTAR,
							H.ID_KARTU, H.NO_KARTU, H.ID_NUM, H.PIN, I.NAMA_JENIS_KARTU, TO_CHAR(H.TGL_AKTIF,'DD/MM/YYYY') TGL_AKTIF, TO_CHAR(H.TGL_EXPIRE,'DD/MM/YYYY') TGL_EXPIRE, NVL(H.ST_PRINT, 'N') ST_PRINT, TO_CHAR(H.TGL_PRINT,'DD/MM/YYYY') TGL_PRINT, NVL(H.ST_AMBIL, 'N') ST_AMBIL, TO_CHAR(H.TGL_AMBIL,'DD/MM/YYYY') TGL_AMBIL 
					FROM	SIS_ANGGOTA A 
							LEFT JOIN SW_PERSON B ON A.PERSON_ID = B.PERSON_ID 
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA C ON A.KD_JENIS_ANGGOTA = C.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERSON_ADDRESS D ON B.PERSON_ID = D.PERSON_ID  AND D.ADDRESS_TYPE_CODE = 'ADR01' 
							LEFT JOIN SW_GENDER E ON B.GENDER_ID = E.GENDER_ID
							LEFT JOIN KEU_BANK F ON A.KD_BANK = F.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON A.ID_PERUSAHAAN = G.ID_PERUSAHAAN 
							JOIN SIS_ANGGOTA_KARTU H ON A.ID_ANGGOTA = H.ID_ANGGOTA 
							JOIN SIS_SETUP_JENIS_KARTU I ON H.KD_JENIS_KARTU = I.KD_JENIS_KARTU
					WHERE	A.DELETE_DATE IS NULL
							".(post('kd_jenis_anggota') != '' ? " AND A.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							AND NVL(PENDING_DOK_DAFTAR,0) = 0
							AND UPPER(NVL(g.id_perusahaan,'')) LIKE '%".strtoupper(post('id_perusahaan'))."%'
							AND UPPER(NVL(g.kode_perusahaan,'')) LIKE '%".strtoupper(post('kode_perusahaan'))."%'
							AND UPPER(NVL(g.nama_perusahaan,'')) LIKE '%".strtoupper(post('nama_perusahaan'))."%'
							AND NVL (H.ST_NONAKTIF, 'N') = 'N' 
							".(in_array(post('st_print'), array('Y','N')) ? " AND NVL(H.ST_PRINT,'N') = '".post('st_print')."' " : '')."
							".(post('st_print') == 'Y' ? 
									(post('tgl_print_start') != '' ? " AND H.TGL_PRINT >= TO_DATE('".post('tgl_print_start')."','DD/MM/YYYY') " : '') .
									(post('tgl_print_to') != '' ? " AND H.TGL_PRINT < TO_DATE('".post('tgl_print_to')."','DD/MM/YYYY')+1 " : '') 
								: '')."
							AND (UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.NAMA_JENIS_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(B.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(B.MOTHER_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(B.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(H.NO_KARTU,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(I.NAMA_JENIS_KARTU,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							
					ORDER BY ".(post('sort')=='' ? " NVL(A.ST_AKTIF,'0'), A.ID_ANGGOTA " : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetKaryawanNonAnggotaJSON()
	{
		$ls_sql	= "	SELECT	A.ID_KARYAWAN_PK, A.NAMA_KARYAWAN, A.TEMPAT_LAHIR, TO_CHAR(A.TGL_LAHIR,'DD/MM/YYYY') TGL_LAHIR,
							A.EMAIL, A.HP, A.NO_KTP, 
							B.GENDER_ID, B.GENDER_NAME, C.ID_AGAMA_PK, C.NAMA_AGAMA, D.ID_JENJANG_PK, D.NAMA_JENJANG
					FROM	HRD_KARYAWAN A
							LEFT JOIN SW_GENDER B ON A.ID_GENDER_FK = B.GENDER_ID
							LEFT JOIN SW_AGAMA C ON A.ID_AGAMA_FK = C.ID_AGAMA_PK
							LEFT JOIN HRD_JENJANG_PENDIDIKAN D ON A.ID_JENJANG_PENDIDIKAN_FK = d.ID_JENJANG_PK
					WHERE	NOT EXISTS(SELECT 1 FROM SW_PERSON X WHERE X.ID_KARYAWAN_PK = A.ID_KARYAWAN_PK)
							AND (UPPER(NVL(A.ID_KARYAWAN_PK,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.NAMA_KARYAWAN,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.TEMPAT_LAHIR,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.EMAIL,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.NO_KTP,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(B.GENDER_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(C.NAMA_AGAMA,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(D.NAMA_JENJANG,'')) LIKE '%".strtoupper(post('keyword'))."%') 
					ORDER BY ".(post('sort')=='' ? 'A.NAMA_KARYAWAN' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
			
	function GetDetail($param=array())
	{
		$this->db->from('SIS_ANGGOTA');
		if(count($param) > 0){
			$this->db->where_in('ID_ANGGOTA', $param);
		}
		$this->db->order_by('ID_ANGGOTA');
		$result = $this->db->get();
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}
	
	function GetDetailLimitKredit($as_id_anggota, $as_kd_jenis_anggota, $ad_tgl_efektif)
	{
		$ls_query	= "	SELECT	A.*,
								NVL (B.PERSEN_LIMIT, F_SIS_GET_LIMIT_PARAMETER ('".$as_kd_jenis_anggota."',
												   A.KODE_JENIS_TRANSAKSI,
												   TO_DATE ('".date_format3($ad_tgl_efektif,'DD/MM/YYYY')."', 'DD/MM/YYYY'))) PERSEN_LIMIT,
								F_SIS_GET_LIMIT_PARAMETER ('".$as_kd_jenis_anggota."',
									A.KODE_JENIS_TRANSAKSI,
									TO_DATE ('".date_format3($ad_tgl_efektif,'DD/MM/YYYY')."', 'DD/MM/YYYY')) STD_PERSEN_LIMIT,
								NVL (B.LIMIT_KREDIT, 0) LIMIT_KREDIT,
								F_SIS_GET_LAST_SISA_TAGIHAN('".$as_id_anggota."', A.KODE_JENIS_TRANSAKSI) SISA_TAGIHAN
						FROM	VW_SIS_JENIS_TRANSAKSI A
								LEFT JOIN SIS_ANGGOTA_LIMIT_KREDIT B ON A.KODE_JENIS_TRANSAKSI = B.KODE_JENIS_TRANSAKSI AND B.ID_ANGGOTA = '".$as_id_anggota."'
						WHERE	A.KODE_JENIS_TRANSAKSI IN ('PINJAM','PJM01','PJM03','MITRA','KONSUMSI')";
		
		$result = $this->db->query($ls_query);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	} 
	
	function Add($arr_data){
		$this->db->insert('SIS_ANGGOTA', $arr_data);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('SIS_ANGGOTA', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_idanggota){
		$result = $this->db->get_where('SIS_ANGGOTA', array('ID_ANGGOTA' => $as_idanggota));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_idanggota)
	{
		$ls_sql	= "	SELECT	A.ID_ANGGOTA, A.ST_AKTIF, TO_CHAR(A.TGL_EXPIRED,'DD/MM/YYYY') TGL_EXPIRED,
							(CASE NVL(A.ST_AKTIF,'0') WHEN '0' THEN 'Daftar' WHEN '1' THEN 'Aktif' ELSE 'Non Aktif' END) NAMA_STATUS,
							A.KD_BANK, F.NAMA_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							B.PERSON_ID, B.ID_KARYAWAN_PK, B.GENDER_ID, B.ID_AGAMA_PK, B.ID_JENJANG_PK, B.KD_KANTOR, B.FULL_NAME, B.MOTHER_NAME,
							B.IDENTITY_TYPE_CODE, B.IDENTITY_NUMBER, B.NIP, B.BIRTH_PLACE, TO_CHAR(B.BIRTH_DATE,'DD/MM/YYYY') BIRTH_DATE, B.EMAIL, B.PHONE, B.USER_ID, 
							C.KD_JENIS_ANGGOTA, C.NAMA_JENIS_ANGGOTA, 
							D.ADDRESS_TYPE_CODE, D.ADDRESS, D.RT, D.RW, D.VILLAGE, D.SUBDISTRICT, 
							D.CITY_CODE, D.PROVINCE_CODE, D.COUNTRY_CODE, D.ZIPCODE, E.GENDER_NAME,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SALDO_SIMPANAN_POKOK,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SALDO_SIMPANAN_WAJIB,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SALDO_SIMPANAN_SUKARELA,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'POKOK') SIMPANAN_POKOK,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'WAJIB') SIMPANAN_WAJIB,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA') SIMPANAN_SUKARELA,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TGL_IURAN') TGL_IURAN,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'GAJI_POKOK') GAJI_POKOK,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TUNJ_RUMAH') TUNJ_RUMAH,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TUNJ_TRANSPORT') TUNJ_TRANSPORT,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TUNJ_LAIN') TUNJ_LAIN,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'POT_PERUSAHAAN') POT_PERUSAHAAN,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'PLAFOND') PLAFOND,
							(SELECT TO_CHAR(MIN(X.TGL_STATUS),'DD/MM/YYYY') TGL_STATUS FROM SIS_ANGGOTA_HISTORY X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.KODE_STATUS_ANGGOTA = 'ST001') TGL_DAFTAR
					FROM	SIS_ANGGOTA A 
							LEFT JOIN SW_PERSON B ON A.PERSON_ID = B.PERSON_ID 
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA C ON A.KD_JENIS_ANGGOTA = C.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERSON_ADDRESS D ON B.PERSON_ID = D.PERSON_ID  AND D.ADDRESS_TYPE_CODE = 'ADR01' 
							LEFT JOIN SW_GENDER E ON B.GENDER_ID = E.GENDER_ID
							LEFT JOIN KEU_BANK F ON A.KD_BANK = F.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON A.ID_PERUSAHAAN = G.ID_PERUSAHAAN
					WHERE	A.ID_ANGGOTA = '".$as_idanggota."'";

		$result = $this->db->query($ls_sql);
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM SIS_PINJAMAN_ANGGOTA WHERE ID_ANGGOTA = '".$arr_rule['id_anggota']."'"))>0 ||
				intval($this->get_data("SELECT COUNT(1) FROM SIS_TRANSAKSI_ANGGOTA WHERE ID_ANGGOTA = '".$arr_rule['id_anggota']."'"))>0 )
			{
				return $this->error_delete.' [id: '.$arr_rule['ID_ANGGOTA']."]<br />";
			}else{
				//$this->db->delete('SIS_ANGGOTA', array('ID_ANGGOTA' => $arr_rule['ID_ANGGOTA']));
				$this->db->update('SIS_ANGGOTA', array('DELETE_USER' => USERID, 'DELETE_DATE' => NOW), array('ID_ANGGOTA' => $arr_rule['id_anggota']));
				$this->db->update('SW_PERSON', array('DELETE_USER' => USERID, 'DELETE_DATE' => NOW), array('PERSON_ID' => $arr_rule['person_id']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
}
?>